2 Data Utilized on the Research

2.1 Data Source

  • New York City Open Data
      - NYPD Complaint Historical Data 2006 to 2017
      - NYC Borough Boundaries (JSON)
  • New York City Planning
      - NYC Borough Population 1900 to 2010
      - NYC Total Housing Units 1940 to 2010
  • Foursquare API

2.2 Data Wrangling and Transformation Approach

New York City Open Data

New York City Planning

Foursquare API

A Foursquare developer account was opened in order to take advantage of its API which provides social networking location information about venues, users, and check-ins. The API enables the research to perform k-means clustering of New York City underlying neighborhood.

3 Methodology

In [42]:
#!conda install -c conda-forge folium=0.5.0 --yes
import folium
from folium import plugins

import numpy as np
import requests # library to handle requests
import pandas as pd
from pandas.io.json import json_normalize # tranform JSON file into a pandas dataframe

#!conda install -c conda-forge geopy --yes # uncomment this line if you haven't completed the Foursquare API lab
from geopy.geocoders import Nominatim # convert an address into latitude and longitude values
import geopy.distance

%matplotlib inline 
import matplotlib as mpl
import matplotlib.pyplot as plt

# Matplotlib and associated plotting modules
import matplotlib.cm as cm
import matplotlib.colors as colors

#import seaborn as sns
mpl.style.use('ggplot') # optional: for ggplot-like style
import json # library to handle JSON files

# import k-means from clustering stage
from sklearn.cluster import KMeans

import os
import webbrowser
from functools import reduce

print('Required libraries imported...')
Required libraries imported...
In [43]:
df_complaint_data = pd.read_csv('nypd_complaint_data_2017.csv')
complaint_data = df_complaint_data
complaint_data.drop(['Cmplnt_Num', 'Month', 'Offense', 'Latitude', 'Longitude'], axis=1, inplace=True)
complaint_data = complaint_data.apply(pd.Series.value_counts).reset_index()
complaint_data.rename(columns={'index':'Neighborhood', 'Borough':'Crime'}, inplace=True)
In [44]:
address = 'New York, NY'
geolocator = Nominatim(user_agent="my-application")
location = geolocator.geocode(address)
nyc_lat = location.latitude
nyc_lon = location.longitude
nyc_zones = r'borough_boundaries.json'

nyc_complaint_map = folium.Map(location=[nyc_lat, nyc_lon],
                          zoom_start=10,
                          tiles='OpenStreetMap')

nyc_complaint_map.choropleth(
    geo_data=nyc_zones,
    data=complaint_data,
    columns=['Neighborhood', 'Crime'],
    key_on='feature.properties.boro_name',
    fill_color='YlOrRd', 
    fill_opacity=0.5, 
    line_opacity=0.5,
    legend_name='2017 New York Boroughs Complaint Data',
    threshold_scale=[25000, 50000, 75000, 100000, 125000, 150000]
)

nyc_complaint_map.save('nyc_complaint_map.html')
nyc_complaint_map
Out[44]:
In [45]:
df_population_data = pd.read_csv('nyc_total_population_1900-2010.csv')
population_data = df_population_data.set_index('Year')
population_data
Out[45]:
Bronx Brooklyn Manhattan Queens Staten Island
Year
1900 200507 1166582 1850093 152999 67021
1910 430980 1634351 2331542 284041 85969
1920 732016 2018356 2284103 469042 116531
1930 1265258 2560401 1867312 1079129 158346
1940 1394711 2698285 1889924 1297634 174441
1950 1451277 2738175 1960101 1550849 191555
1960 1424815 2627319 1698281 1809578 221991
1970 1471701 2602012 1539233 1986473 295443
1980 1168972 2230936 1428285 1891325 352121
1990 1203789 2300664 1487536 1951598 378977
2000 1332650 2465326 1537195 2229379 443728
2010 1385108 2504700 1585873 2230722 468730
In [46]:
population_data.plot(kind='area', alpha=0.3, figsize=(18, 8), stacked=False, linewidth=3) # area plo
plt.title("New York City Population from 1900 to 2010", loc="left", fontsize=14)
plt.legend(loc="upper left")
plt.ylabel("POPULATION", fontsize=12)
plt.xlabel("YEAR", fontsize=12)
plt.savefig('nyc_population.png')
plt.show()
In [47]:
df_housing_data = pd.read_csv('nyc_housing_unit_1940-2010.csv')
housing_data = df_housing_data.set_index('Year')
housing_data
Out[47]:
Bronx Brooklyn Manhattan Queens Staten Island
Year
1940 395245 762526 617373 394389 48839
1950 432259 814134 635944 495308 55820
1960 473160 875446 727432 616922 65156
1970 508789 902622 714593 708419 89961
1980 451118 881367 754796 740129 119000
1990 440955 873671 785127 752690 139726
2000 490659 930866 798144 817250 163993
2010 511896 1000293 847090 835127 176656
In [48]:
housing_data.plot(kind='area', alpha=0.3, figsize=(18, 8), stacked=False, linewidth=3) # area plo
plt.title("New York City Housing Unit from 1940 to 2010", loc="left", fontsize=14)
plt.legend(loc="upper left")
plt.ylabel("HOUSING UNIT", fontsize=14)
plt.xlabel("YEAR", fontsize=14)
plt.savefig('nyc_housing.png')
plt.show()
In [49]:
df_complaint_hist = pd.read_csv('nypd_complaint_data_2006-2017.csv')
complaint_hist = df_complaint_hist.set_index('Year')
complaint_hist
Out[49]:
Bronx Brooklyn Manhattan Queens Staten Island
Year
2006 111263 157808 127654 105425 27022
2007 117154 155830 130216 105634 26126
2008 114191 155632 128514 102885 26735
2009 112973 150739 124811 99187 24276
2010 111566 151482 121391 98575 24114
2011 108657 151497 115844 98639 23424
2012 106790 154885 119706 98927 23614
2013 104421 150184 118294 101291 22721
2014 105861 148700 113345 100245 22846
2015 104912 143116 113231 94842 22131
2016 106050 140601 115624 94461 21831
2017 103505 137701 114615 91577 21295
In [81]:
complaint_hist.plot(kind='area', alpha=0.5, figsize=(18, 8), stacked=False, linewidth=3) # area plo
plt.title("NYPD Complaint Data from 2006 to 2017", loc="left", fontsize=16)
plt.legend(loc="upper left")
plt.ylabel("COMPLAINTS", fontsize=14)
plt.xlabel("YEAR", fontsize=14)
plt.savefig('nyc_complaint.png')
plt.show()
In [51]:
df_complaint_smry = pd.read_csv('nyc_complaint_data_comparison_2017.csv', index_col=0)
df_complaint_smry
Out[51]:
Violation Misdemeanor Felony
Borough
Bronx 14.86 57.28 27.86
Brooklyn 14.38 53.47 32.15
Manhattan 11.61 57.38 31.02
Queens 15.57 52.29 32.14
Staten Island 21.72 55.85 22.43
In [52]:
colors_list = ['#f7dc6f','#d68910','#a93226']

# Change this line to plot percentages instead of absolute values
ax = (df_complaint_smry.div(df_complaint_smry.sum(1), axis=0)).plot(kind='bar', alpha=0.65, figsize=(18,8), width = 0.95, color = colors_list)

plt.legend(labels=df_complaint_smry.columns,fontsize=14, loc=1)
plt.title("New York Boroughs 2017 Complaint Data Comparison", fontsize=14)
plt.legend(loc='upper left')

plt.xticks(fontsize=12)
for spine in plt.gca().spines.values():
    spine.set_visible(False)
plt.yticks([])

# Add this loop to add the annotations
for p in ax.patches:
    width, height = p.get_width(), p.get_height()
    x, y = p.get_xy() 
    ax.annotate('{:.2%}'.format(height), (p.get_x()+.15*width, p.get_y() + height + 0.01))
    
plt.savefig('nyc_complaint_comparison.png')
In [53]:
df_complaint_data = pd.read_csv('nypd_complaint_data_2017.csv')
df_complaint_data = df_complaint_data[df_complaint_data['Borough'] == 'Queens'].reset_index(drop=True)
df_complaint_data = df_complaint_data.sample(frac=.0025, random_state=0)
borough_data = df_complaint_data
borough_data.drop(['Cmplnt_Num', 'Month'], axis=1, inplace=True)
borough_data.shape
Out[53]:
(229, 4)
In [54]:
address = 'Queens, NY'
geolocator = Nominatim(user_agent="my-application")
location = geolocator.geocode(address)
borough_lat = location.latitude
borough_lon = location.longitude

borough_map = folium.Map(location=[borough_lat, borough_lon], 
                           zoom_start=11, 
                           tiles='OpenStreetMap')

incidents = plugins.MarkerCluster().add_to(borough_map)

# loop through the dataframe and add each data point to the mark cluster
for lat, lng, label, in zip(borough_data.Latitude, borough_data.Longitude, borough_data.Offense):
    folium.Marker(
        location=[lat, lng],
        icon=None,
        popup=label,
    ).add_to(incidents)

borough_map.save('borough_map.html')
# display map
borough_map
Out[54]:
In [55]:
crime_data = df_complaint_data
crime_data['Neighborhood'] = ''

def reverse_geocode(coordinates):
    # Google Map API call to derive JSON address given longitude and latitude
    api_key = 'AIzaSyAC17OaVd0IOBC0cXktIKBdUepzI5JpciQ'
    url = 'https://maps.googleapis.com/maps/api/geocode/json?latlng={0}&key={1}'
    r = url.format(coordinates, api_key)
    data = json.loads(requests.get(r).text)
    
    # loop over JSON address components to get city location
    neighborhood = ''
    if len(data['results']) > 0:
        for component in data['results'][0]['address_components']:
            if 'neighborhood' in component['types']:
                neighborhood = component['long_name']
            elif 'locality' in component['types']:
                neighborhood = component['long_name']
            elif 'postal_town' in component['types']:
                neighborhood = component['long_name']
            else:
                continue
                
    return neighborhood

for i, row in crime_data.iterrows():
    crime_data['Neighborhood'][i] = reverse_geocode(crime_data['Latitude'][i].astype(str) + ',' + crime_data['Longitude'][i].astype(str))
    
crime_data.head()
/home/jupyterlab/conda/lib/python3.6/site-packages/ipykernel_launcher.py:27: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
Out[55]:
Offense Borough Latitude Longitude Neighborhood
9145 DANGEROUS WEAPONS Queens 40.759578 -73.830141 Flushing
35623 FELONY ASSAULT Queens 40.679981 -73.776234 Jamaica
88066 CRIMINAL MISCHIEF Queens 40.776335 -73.935776 Long Island City
63073 OFFENSES AGAINST PUBLIC SAFETY Queens 40.689489 -73.857493 Woodhaven
46799 CRIMINAL MISCHIEF Queens 40.755656 -73.942051 Long Island City
In [56]:
crime_aggr = crime_data.groupby(['Borough', 'Neighborhood'])['Offense'].count().reset_index()
crime_aggr
Out[56]:
Borough Neighborhood Offense
0 Queens 1
1 Queens Arverne 2
2 Queens Astoria 5
3 Queens Bayside 1
4 Queens Broad Channel 1
5 Queens College Point 3
6 Queens Corona 6
7 Queens Ditmars Steinway 1
8 Queens East New York 1
9 Queens Elmhurst 4
10 Queens Far Rockaway 11
11 Queens Flushing 50
12 Queens Forest Hills 3
13 Queens Fresh Meadows 2
14 Queens Glen Oaks 1
15 Queens Glendale 1
16 Queens Hollis 1
17 Queens Jackson Heights 7
18 Queens Jamaica 70
19 Queens Laurelton 1
20 Queens Little Neck 1
21 Queens Long Island City 21
22 Queens Middle Village 1
23 Queens North Corona 2
24 Queens Oakland Gardens 1
25 Queens Ozone Park 4
26 Queens Queens Village 4
27 Queens Rego Park 5
28 Queens Richmond Hill 2
29 Queens Ridgewood 2
30 Queens Rochdale 1
31 Queens Rockaway Park 2
32 Queens Rosedale 1
33 Queens South Ozone Park 2
34 Queens South Richmond Hill 1
35 Queens St. Albans 1
36 Queens Woodhaven 1
37 Queens Woodside 5
In [57]:
#!wget -q -O 'newyork_data.json' https://ibm.box.com/shared/static/fbpwbovar7lf8p5sgddm06cgipa2rxpe.json
#
#with open('newyork_data.json') as json_data:
#    newyork_data = json.load(json_data)
#    
#neighborhoods_data = newyork_data['features']
#
# define the dataframe columns
#column_names = ['Borough', 'Neighborhood', 'Latitude', 'Longitude'] 
#
# instantiate the dataframe
#neighborhoods = pd.DataFrame(columns=column_names)
#
#for data in neighborhoods_data:
#    borough = neighborhood_name = data['properties']['borough'] 
#    neighborhood_name = data['properties']['name']
#        
#    neighborhood_latlon = data['geometry']['coordinates']
#    neighborhood_lat = neighborhood_latlon[1]
#    neighborhood_lon = neighborhood_latlon[0]
#    
#    neighborhoods = neighborhoods.append({'Borough': borough,
#                                          'Neighborhood': neighborhood_name,
#                                          'Latitude': neighborhood_lat,
#                                          'Longitude': neighborhood_lon}, ignore_index=True)
#
#borough_data = neighborhoods[neighborhoods['Borough'] == 'Queens'].reset_index(drop=True)
#borough_data.to_csv('queens_borough_data.csv')
In [58]:
borough_data = pd.read_csv('queens_borough_data.csv')
borough_data
Out[58]:
Borough Neighborhood Latitude Longitude
0 Queens Astoria 40.768509 -73.915654
1 Queens Woodside 40.746349 -73.901842
2 Queens Jackson Heights 40.751981 -73.882821
3 Queens Elmhurst 40.744049 -73.881656
4 Queens Howard Beach 40.654225 -73.838138
5 Queens Corona 40.742382 -73.856825
6 Queens Forest Hills 40.725264 -73.844475
7 Queens Kew Gardens 40.705179 -73.829819
8 Queens Richmond Hill 40.697947 -73.831833
9 Queens Flushing 40.764454 -73.831773
10 Queens Long Island City 40.750217 -73.939202
11 Queens Sunnyside 40.740176 -73.926916
12 Queens East Elmhurst 40.764073 -73.867041
13 Queens Maspeth 40.725427 -73.896217
14 Queens Ridgewood 40.708323 -73.901435
15 Queens Glendale 40.702762 -73.870742
16 Queens Rego Park 40.728974 -73.857827
17 Queens Woodhaven 40.689887 -73.858110
18 Queens Ozone Park 40.680708 -73.843203
19 Queens South Ozone Park 40.668550 -73.809865
20 Queens College Point 40.784903 -73.843045
21 Queens Whitestone 40.781291 -73.814202
22 Queens Bayside 40.766041 -73.774274
23 Queens Auburndale 40.761730 -73.791762
24 Queens Little Neck 40.770826 -73.738898
25 Queens Douglaston 40.766846 -73.742498
26 Queens Glen Oaks 40.749441 -73.715481
27 Queens Bellerose 40.728573 -73.720128
28 Queens Kew Gardens Hills 40.722578 -73.820878
29 Queens Fresh Meadows 40.734394 -73.782713
... ... ... ... ...
56 Queens Hillcrest 40.723825 -73.797603
57 Queens Ravenswood 40.761705 -73.931575
58 Queens Lindenwood 40.663918 -73.849638
59 Queens Laurelton 40.667884 -73.740256
60 Queens Lefrak City 40.736075 -73.862525
61 Queens Belle Harbor 40.576156 -73.854017
62 Queens Rockaway Park 40.580343 -73.841534
63 Queens Somerville 40.597711 -73.796648
64 Queens Brookville 40.660003 -73.751753
65 Queens Bellaire 40.733014 -73.738892
66 Queens North Corona 40.754071 -73.857518
67 Queens Forest Hills Gardens 40.714611 -73.841022
68 Queens Jamaica Hills 40.711460 -73.796465
69 Queens Utopia 40.733500 -73.796717
70 Queens Pomonok 40.734936 -73.804861
71 Queens Astoria Heights 40.770317 -73.894680
72 Queens Hunters Point 40.743414 -73.953868
73 Queens Sunnyside Gardens 40.745652 -73.918193
74 Queens Blissville 40.737251 -73.932442
75 Queens Roxbury 40.567376 -73.892138
76 Queens Middle Village 40.716415 -73.881143
77 Queens Malba 40.790602 -73.826678
78 Queens Hammels 40.587338 -73.805530
79 Queens Bayswater 40.611322 -73.765968
80 Queens Queensbridge 40.756091 -73.945631
81 Queens Jamaica 40.702677 -73.788969
82 Queens Bellerose Manor 40.732021 -73.718364
83 Queens Ditmars Steinway 40.776741 -73.909528
84 Queens Forest Park 40.700023 -73.856609
85 Queens South Richmond Hill 40.691893 -73.825733

86 rows × 4 columns

In [59]:
# create map of given borough latitude and longitude values
borough_map = folium.Map(location=[borough_lat, borough_lon], zoom_start=10)

# add markers to map
for lat, lng, label in zip(borough_data['Latitude'], borough_data['Longitude'], borough_data['Neighborhood']):
    label = folium.Popup(label, parse_html=True)
    folium.CircleMarker(
        [lat, lng],
        radius=5,
        popup=label,
        color='blue',
        fill=True,
        fill_color='#3186cc',
        fill_opacity=0.7,
        parse_html=False).add_to(borough_map)  

borough_map.save('borough_cluster_from.html')
borough_map
Out[59]:
In [60]:
# @hidden_cell
CLIENT_ID = 'OYCZWB2YCGWZTR5XWBJCOIBZDH4AWI0X5YQ00A3G0OOQITV3' # your Foursquare ID
CLIENT_SECRET = 'B5D2U2XKJPGO5ZA4ESDU5KDHN4NU45GFL4KOI2D5RVRS3GY2' # your Foursquare Secret
VERSION = '20180605' # Foursquare API version
In [61]:
borough_data.loc[0, 'Neighborhood']

neighborhood_latitude = borough_data.loc[0, 'Latitude'] # neighborhood latitude value
neighborhood_longitude = borough_data.loc[0, 'Longitude'] # neighborhood longitude value

neighborhood_name = borough_data.loc[0, 'Neighborhood'] # neighborhood name

LIMIT = 100 # limit of number of venues returned by Foursquare API
radius = 2500 # define radius

url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
    CLIENT_ID, 
    CLIENT_SECRET, 
    VERSION, 
    neighborhood_latitude, 
    neighborhood_longitude, 
    radius, 
    LIMIT)

results = requests.get(url).json()
In [62]:
def getNearbyVenues(names, latitudes, longitudes, radius=500):
    
    venues_list=[]
    for name, lat, lng in zip(names, latitudes, longitudes):
        # print(name)
            
        # create the API request URL
        url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
            CLIENT_ID, 
            CLIENT_SECRET, 
            VERSION, 
            lat, 
            lng, 
            radius, 
            LIMIT)
            
        # make the GET request
        results = requests.get(url).json()["response"]['groups'][0]['items']
        
        # return only relevant information for each nearby venue
        venues_list.append([(
            name, 
            lat, 
            lng, 
            v['venue']['name'], 
            v['venue']['location']['lat'], 
            v['venue']['location']['lng'],  
            v['venue']['categories'][0]['name']) for v in results])

    nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
    nearby_venues.columns = ['Neighborhood', 
                  'Neighborhood Latitude', 
                  'Neighborhood Longitude', 
                  'Venue', 
                  'Venue Latitude', 
                  'Venue Longitude', 
                  'Venue Category']
    
    return(nearby_venues)

borough_venues = getNearbyVenues(names=borough_data['Neighborhood'],
                                   latitudes=borough_data['Latitude'],
                                   longitudes=borough_data['Longitude']
                                  )
In [63]:
print(borough_venues.shape)
borough_venues.head()
df_borough_venues = pd.DataFrame(borough_venues, columns = ['Neighborhood', 'Neighborhood Latitude', 'Neighborhood Longitude', 
                                                      'Venue', 'Venue Latitude', 'Venue Longitude', 'Venue Category'])
df_borough_venues.to_csv('borough_venues.csv')
(2271, 7)
In [64]:
borough_venues
Out[64]:
Neighborhood Neighborhood Latitude Neighborhood Longitude Venue Venue Latitude Venue Longitude Venue Category
0 Astoria 40.768509 -73.915654 Favela Grill 40.767348 -73.917897 Brazilian Restaurant
1 Astoria 40.768509 -73.915654 Orange Blossom 40.769856 -73.917012 Gourmet Shop
2 Astoria 40.768509 -73.915654 Titan Foods Inc. 40.769198 -73.919253 Gourmet Shop
3 Astoria 40.768509 -73.915654 CrossFit Queens 40.769404 -73.918977 Gym
4 Astoria 40.768509 -73.915654 Off The Hook 40.767200 -73.918104 Seafood Restaurant
5 Astoria 40.768509 -73.915654 Simply Fit Astoria 40.769114 -73.912403 Gym
6 Astoria 40.768509 -73.915654 Irish Rover 40.765835 -73.914647 Pub
7 Astoria 40.768509 -73.915654 Sitan Muay Thai 40.766108 -73.913224 Martial Arts Dojo
8 Astoria 40.768509 -73.915654 Al-sham Sweets and Pastries 40.768077 -73.911561 Middle Eastern Restaurant
9 Astoria 40.768509 -73.915654 El Cafetal 40.770153 -73.918214 Mexican Restaurant
10 Astoria 40.768509 -73.915654 SVL Bar 40.769816 -73.918530 Greek Restaurant
11 Astoria 40.768509 -73.915654 Neptune Diner 40.770788 -73.916890 Diner
12 Astoria 40.768509 -73.915654 Mazag Hookah Lounge 40.766157 -73.913212 Hookah Bar
13 Astoria 40.768509 -73.915654 Sabry's 40.768446 -73.911267 Seafood Restaurant
14 Astoria 40.768509 -73.915654 Duzan 40.768730 -73.911013 Falafel Restaurant
15 Astoria 40.768509 -73.915654 Farid Kebab, Steinway 40.767548 -73.911972 Mediterranean Restaurant
16 Astoria 40.768509 -73.915654 Noisette 40.768544 -73.911248 Dessert Shop
17 Astoria 40.768509 -73.915654 Brooklyn Bagel & Coffee Co. 40.764895 -73.916954 Bagel Shop
18 Astoria 40.768509 -73.915654 Ample Hills Creamery 40.765562 -73.919012 Ice Cream Shop
19 Astoria 40.768509 -73.915654 GiGi Salon Styling Studio - Aveda Concept Salon 40.765707 -73.918851 Health & Beauty Service
20 Astoria 40.768509 -73.915654 Leli's Bakery and Pastry Shop 40.765132 -73.917696 Bakery
21 Astoria 40.768509 -73.915654 Ovelia Psistaria Bar 40.765779 -73.918890 Greek Restaurant
22 Astoria 40.768509 -73.915654 Seva Indian Cuisine 40.765521 -73.919157 Indian Restaurant
23 Astoria 40.768509 -73.915654 Create & Go 40.765021 -73.917172 Salad Place
24 Astoria 40.768509 -73.915654 AbuQir Seafood 40.768484 -73.910955 Seafood Restaurant
25 Astoria 40.768509 -73.915654 Sweet Afton 40.765350 -73.918983 Bar
26 Astoria 40.768509 -73.915654 Republic 40.770229 -73.914691 Italian Restaurant
27 Astoria 40.768509 -73.915654 Basil Brick Oven Pizza 40.770865 -73.920080 Pizza Place
28 Astoria 40.768509 -73.915654 Cold Stone Creamery 40.765696 -73.913778 Ice Cream Shop
29 Astoria 40.768509 -73.915654 Chip 40.765588 -73.919144 Bakery
... ... ... ... ... ... ... ...
2241 Ditmars Steinway 40.776741 -73.909528 Belief 40.774685 -73.913379 Board Shop
2242 Ditmars Steinway 40.776741 -73.909528 Dr. Socrates Kangadis 40.774178 -73.913073 Doctor's Office
2243 Ditmars Steinway 40.776741 -73.909528 Loose Leaf 40.775040 -73.914060 Tea Room
2244 Ditmars Steinway 40.776741 -73.909528 K&D Inter Jewelry 40.773527 -73.906436 Jewelry Store
2245 Forest Park 40.700023 -73.856609 Victory Field Jogging Track 40.700837 -73.852701 Track
2246 Forest Park 40.700023 -73.856609 Forest Park Carousel 40.700301 -73.856676 Theme Park Ride / Attraction
2247 Forest Park 40.700023 -73.856609 Victory Field 40.701241 -73.853441 Baseball Field
2248 Forest Park 40.700023 -73.856609 Forest Park Dog Run 40.696710 -73.860400 Dog Run
2249 Forest Park 40.700023 -73.856609 Forest Park Bandshell 40.698936 -73.857978 Music Venue
2250 Forest Park 40.700023 -73.856609 baha Lounge 40.702256 -73.855663 Lounge
2251 Forest Park 40.700023 -73.856609 Dunkin' Donuts 40.702810 -73.854725 Donut Shop
2252 Forest Park 40.700023 -73.856609 Nyc Century Forest park rest stop 40.699016 -73.857760 Park
2253 Forest Park 40.700023 -73.856609 George Seuffert Bandshell 40.699030 -73.857955 Music Venue
2254 Forest Park 40.700023 -73.856609 MTA Bus - Q11/Q21/Q52/Q53 (Woodhaven Blvd/Myrt... 40.702239 -73.854976 Bus Line
2255 Forest Park 40.700023 -73.856609 Piccola Roma Deli 40.703058 -73.858502 Deli / Bodega
2256 Forest Park 40.700023 -73.856609 ivan's coffee shop 40.697796 -73.851558 Coffee Shop
2257 South Richmond Hill 40.691893 -73.825733 Sohna Punjab 40.693666 -73.829235 Indian Restaurant
2258 South Richmond Hill 40.691893 -73.825733 Hibiscus 40.691299 -73.821147 Caribbean Restaurant
2259 South Richmond Hill 40.691893 -73.825733 The Nest Restaurant 40.691697 -73.820287 Caribbean Restaurant
2260 South Richmond Hill 40.691893 -73.825733 India Cafe 40.693838 -73.827767 Indian Restaurant
2261 South Richmond Hill 40.691893 -73.825733 Lenihans 40.694362 -73.827838 Bar
2262 South Richmond Hill 40.691893 -73.825733 Papa John's Pizza 40.693392 -73.829147 Pizza Place
2263 South Richmond Hill 40.691893 -73.825733 Royal India Palace 40.693868 -73.828480 Indian Restaurant
2264 South Richmond Hill 40.691893 -73.825733 Key Food 40.690779 -73.822503 Supermarket
2265 South Richmond Hill 40.691893 -73.825733 Punjabi Kabab House 40.694523 -73.828254 Indian Restaurant
2266 South Richmond Hill 40.691893 -73.825733 Tandoori Hut 40.693930 -73.827545 Indian Restaurant
2267 South Richmond Hill 40.691893 -73.825733 Guyana's Choice Bakery 40.689998 -73.825666 Bakery
2268 South Richmond Hill 40.691893 -73.825733 Villa Gardens Famous Pizza 40.694446 -73.827308 Pizza Place
2269 South Richmond Hill 40.691893 -73.825733 Lt. Frank McConnell Park 40.694270 -73.829010 Park
2270 South Richmond Hill 40.691893 -73.825733 Phil "Scooter" Rizzuto Park 40.694487 -73.821847 Park

2271 rows × 7 columns

In [65]:
#borough_venues.groupby(['Neighborhood',
#                        'Neighborhood Latitude',
#                        'Neighborhood Longitude',
#                        'Venue Category']
#                      )['Venue'].count()
borough_venues_aggr = borough_venues.groupby(['Neighborhood'])['Venue'].count().reset_index()
In [66]:
print('There are {} uniques categories.'.format(len(borough_venues['Venue Category'].unique())))
There are 275 uniques categories.
In [67]:
# one hot encoding
borough_onehot = pd.get_dummies(borough_venues[['Venue Category']], prefix="", prefix_sep="")

# add neighborhood column back to dataframe
borough_onehot['Neighborhood'] = borough_venues['Neighborhood'] 

# move neighborhood column to the first column
fixed_columns = [borough_onehot.columns[-1]] + list(borough_onehot.columns[:-1])
borough_onehot = borough_onehot[fixed_columns]

borough_grouped = borough_onehot.groupby('Neighborhood').mean().reset_index()

def return_most_common_venues(row, num_top_venues):
    row_categories = row.iloc[1:]
    row_categories_sorted = row_categories.sort_values(ascending=False)
    
    return row_categories_sorted.index.values[0:num_top_venues]

num_top_venues = 10

indicators = ['st', 'nd', 'rd']

# create columns according to number of top venues
columns = ['Neighborhood']
for ind in np.arange(num_top_venues):
    try:
        columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))
    except:
        columns.append('{}th Most Common Venue'.format(ind+1))

# create a new dataframe
neighborhoods_venues_sorted = pd.DataFrame(columns=columns)
neighborhoods_venues_sorted['Neighborhood'] = borough_grouped['Neighborhood']

for ind in np.arange(borough_grouped.shape[0]):
    neighborhoods_venues_sorted.iloc[ind, 1:] = return_most_common_venues(borough_grouped.iloc[ind, :], num_top_venues)

neighborhoods_venues_sorted
Out[67]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
0 Arverne Surf Spot Metro Station Thai Restaurant Board Shop Beach Sandwich Place Donut Shop Bed & Breakfast Bus Stop Playground
1 Astoria Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant
2 Astoria Heights Chinese Restaurant Deli / Bodega Bakery Supermarket Italian Restaurant Bowling Alley Playground Plaza Pizza Place Burger Joint
3 Auburndale Fast Food Restaurant Train Station Donut Shop Korean Restaurant Noodle House Bar Discount Store Italian Restaurant Toy / Game Store Supermarket
4 Bay Terrace Clothing Store Women's Store Mobile Phone Shop Donut Shop Lingerie Store Cosmetics Shop Kids Store Supermarket Bank Coffee Shop
5 Bayside Bar Pizza Place Spa American Restaurant Donut Shop Indian Restaurant Sushi Restaurant South American Restaurant Pub Mexican Restaurant
6 Bayswater Tennis Court Playground Park Women's Store Event Space Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Falafel Restaurant
7 Beechhurst Chinese Restaurant Yoga Studio Italian Restaurant Donut Shop Shopping Mall Gym Gym / Fitness Center Dessert Shop Deli / Bodega Pizza Place
8 Bellaire Moving Target Deli / Bodega IT Services Convenience Store Construction & Landscaping Bus Station Laundromat Italian Restaurant Chinese Restaurant Halal Restaurant
9 Belle Harbor Beach Deli / Bodega Boutique Spa Pharmacy Bagel Shop Ice Cream Shop Chinese Restaurant Italian Restaurant Donut Shop
10 Bellerose Chinese Restaurant Pizza Place Italian Restaurant Deli / Bodega Donut Shop Gas Station Salon / Barbershop Grocery Store Diner Bank
11 Bellerose Manor Mobile Phone Shop Indian Restaurant Grocery Store Hookah Bar Farmers Market Fish & Chips Shop Fish Market Filipino Restaurant Fast Food Restaurant Dry Cleaner
12 Blissville Hotel Donut Shop Deli / Bodega Bus Station Clothing Store Rental Service Bar Electronics Store Restaurant Mattress Store
13 Breezy Point Beach Monument / Landmark American Restaurant Board Shop Supermarket Trail Women's Store Farm Electronics Store Empanada Restaurant
14 Briarwood Deli / Bodega Pet Store Playground Mexican Restaurant Indian Restaurant Arts & Crafts Store Fast Food Restaurant Bus Station Coffee Shop Farm
15 Broad Channel Other Nightlife Dive Bar Deli / Bodega Pizza Place Event Space Dumpling Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant
16 Brookville Deli / Bodega Women's Store Farmers Market Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farm Fast Food Restaurant
17 Cambria Heights Caribbean Restaurant Chinese Restaurant Cosmetics Shop Moving Target Flower Shop Dentist's Office Health Food Store Pharmacy Bus Station Liquor Store
18 College Point Bakery Deli / Bodega Chinese Restaurant Pizza Place Bar Latin American Restaurant Pharmacy Supermarket Asian Restaurant Donut Shop
19 Corona Mexican Restaurant Empanada Restaurant Chinese Restaurant Sandwich Place Bakery Supermarket Restaurant Food Truck Park Italian Restaurant
20 Ditmars Steinway Deli / Bodega Greek Restaurant Bakery Italian Restaurant Bagel Shop Pizza Place Sushi Restaurant Grocery Store Bar Donut Shop
21 Douglaston Deli / Bodega Chinese Restaurant Italian Restaurant Yoga Studio Coffee Shop Spa Fast Food Restaurant Shipping Store Supermarket Lounge
22 East Elmhurst Donut Shop Rental Car Location Chinese Restaurant Lake Gas Station Electronics Store Coffee Shop Supermarket Ice Cream Shop Hotel Bar
23 Edgemere Pizza Place Metro Station Gift Shop Bus Station Scenic Lookout Grocery Store Skate Park Park Asian Restaurant Farm
24 Elmhurst Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop
25 Far Rockaway Pizza Place Pharmacy Grocery Store Fast Food Restaurant Breakfast Spot Chinese Restaurant Other Great Outdoors Donut Shop Sandwich Place Bank
26 Floral Park Indian Restaurant Grocery Store Basketball Court Pizza Place Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space
27 Flushing Chinese Restaurant Bubble Tea Shop Korean Restaurant Karaoke Bar Hotpot Restaurant Bakery Construction & Landscaping Supermarket Hotel Gym / Fitness Center
28 Forest Hills Gym Yoga Studio Park Gym / Fitness Center Thai Restaurant Pizza Place Convenience Store Pharmacy Pool Deli / Bodega
29 Forest Hills Gardens Bakery Grocery Store Playground Park New American Restaurant Sandwich Place Tapas Restaurant Noodle House Mexican Restaurant Bagel Shop
... ... ... ... ... ... ... ... ... ... ... ...
56 Neponsit Beach Bar Women's Store Farm Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farmers Market Eastern European Restaurant
57 North Corona Deli / Bodega Gym / Fitness Center Pizza Place Bakery Fast Food Restaurant Latin American Restaurant Caribbean Restaurant Mobile Phone Shop Museum Gym
58 Oakland Gardens Korean Restaurant Chinese Restaurant Yoga Studio Donut Shop Supplement Shop Sushi Restaurant Supermarket Taiwanese Restaurant Bank Bagel Shop
59 Ozone Park Pharmacy Pizza Place Deli / Bodega Diner Bank Gym Fast Food Restaurant Bowling Alley Sporting Goods Shop Breakfast Spot
60 Pomonok Bus Station Playground Japanese Restaurant Bowling Alley Supermarket Bar Scenic Lookout Pizza Place American Restaurant Deli / Bodega
61 Queens Village Bank Discount Store Fast Food Restaurant Bus Station Bus Stop Martial Arts Dojo Donut Shop Sandwich Place Mobile Phone Shop Bakery
62 Queensboro Hill Chinese Restaurant Bank Bakery Pizza Place Asian Restaurant Supermarket Taiwanese Restaurant Bar Bagel Shop Frozen Yogurt Shop
63 Queensbridge Hotel Sandwich Place Athletics & Sports Ramen Restaurant Baseball Field Roof Deck Park Scenic Lookout Hotel Bar Hotel Pool
64 Ravenswood Grocery Store Spanish Restaurant Liquor Store Chinese Restaurant Latin American Restaurant Sushi Restaurant Market Taco Place Fried Chicken Joint Korean Restaurant
65 Rego Park Bakery Chinese Restaurant Grocery Store Sandwich Place Donut Shop Sushi Restaurant Restaurant Pizza Place Pharmacy Martial Arts Dojo
66 Richmond Hill Latin American Restaurant Pizza Place Discount Store Lounge Caribbean Restaurant Clothing Store Sandwich Place Diner Park Gym / Fitness Center
67 Ridgewood Grocery Store Bank Restaurant Italian Restaurant Pizza Place Deli / Bodega Greek Restaurant Sushi Restaurant Noodle House Supplement Shop
68 Rochdale Market Southern / Soul Food Restaurant Food Court Chinese Restaurant Bank Sandwich Place Liquor Store Candy Store Laundromat Bus Stop
69 Rockaway Beach Beach Ice Cream Shop Brazilian Restaurant Latin American Restaurant Seafood Restaurant Bagel Shop BBQ Joint Metro Station Pizza Place Arepa Restaurant
70 Rockaway Park Beach Pizza Place Bank Steakhouse Bus Stop Board Shop French Restaurant Smoke Shop Seafood Restaurant Bagel Shop
71 Rosedale Fried Chicken Joint Bus Station Pharmacy Caribbean Restaurant Supermarket Jewelry Store Sandwich Place Deli / Bodega Cosmetics Shop Accessories Store
72 Roxbury Beach Trail Pub Pizza Place Fast Food Restaurant Deli / Bodega Irish Pub Women's Store Eastern European Restaurant Egyptian Restaurant
73 Somerville Park Women's Store Falafel Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Farm Dry Cleaner
74 South Jamaica Caribbean Restaurant Supermarket Grocery Store Shop & Service Park Bus Station Dessert Shop Bakery Farm Farmers Market
75 South Ozone Park Deli / Bodega Park Bar Hotel Donut Shop Food Truck Fast Food Restaurant Sandwich Place Electronics Store Dumpling Restaurant
76 South Richmond Hill Indian Restaurant Caribbean Restaurant Pizza Place Park Bakery Supermarket Bar Event Space Eastern European Restaurant Egyptian Restaurant
77 Springfield Gardens Donut Shop Bus Station Fried Chicken Joint Chinese Restaurant Park Check Cashing Service Liquor Store Gym Shoe Store Laundromat
78 St. Albans Caribbean Restaurant Deli / Bodega Convenience Store Discount Store Fast Food Restaurant Fried Chicken Joint Café Shopping Mall Market Electronics Store
79 Steinway Deli / Bodega Rental Car Location Cosmetics Shop Sushi Restaurant Women's Store Grocery Store Dessert Shop Donut Shop Chinese Restaurant Café
80 Sunnyside Pizza Place Italian Restaurant Chinese Restaurant Discount Store Coffee Shop South American Restaurant Bakery Hotel Supermarket Romanian Restaurant
81 Sunnyside Gardens Bar American Restaurant Grocery Store Coffee Shop Pizza Place Pharmacy Thai Restaurant Filipino Restaurant Deli / Bodega Mexican Restaurant
82 Utopia Deli / Bodega Chinese Restaurant South American Restaurant Paper / Office Supplies Store Automotive Shop Basketball Court Donut Shop Bus Stop Playground Indie Movie Theater
83 Whitestone Bubble Tea Shop Deli / Bodega Moving Target Train Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space
84 Woodhaven Deli / Bodega Bank Pharmacy Park Donut Shop Liquor Store Bagel Shop Dive Bar Shoe Store Fast Food Restaurant
85 Woodside Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center

86 rows × 11 columns

In [68]:
# set number of clusters
kclusters = 5

borough_grouped_clustering = borough_grouped.drop('Neighborhood', 1)

# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(borough_grouped_clustering)

# check cluster labels generated for each row in the dataframe
kmeans.labels_[0:10] 
Out[68]:
array([0, 0, 0, 0, 0, 0, 1, 0, 0, 0], dtype=int32)
In [69]:
borough_merged = borough_data

# add clustering labels
borough_merged['Cluster Labels'] = kmeans.labels_

# merge borough_grouped with borough_data to add latitude/longitude for each neighborhood
borough_merged = borough_merged.join(neighborhoods_venues_sorted.set_index('Neighborhood'), on='Neighborhood')

borough_merged.head() # check the last columns!
Out[69]:
Borough Neighborhood Latitude Longitude Cluster Labels 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
0 Queens Astoria 40.768509 -73.915654 0 Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant
1 Queens Woodside 40.746349 -73.901842 0 Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center
2 Queens Jackson Heights 40.751981 -73.882821 0 Latin American Restaurant Mobile Phone Shop South American Restaurant Peruvian Restaurant Mexican Restaurant Bakery Diner Kids Store Spanish Restaurant Thai Restaurant
3 Queens Elmhurst 40.744049 -73.881656 0 Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop
4 Queens Howard Beach 40.654225 -73.838138 0 Italian Restaurant Bagel Shop Sandwich Place Pharmacy Bank Spa Clothing Store Fast Food Restaurant Event Space Fried Chicken Joint
In [70]:
# create map
map_clusters = folium.Map(location=[borough_lat, borough_lon], zoom_start=11)

# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i+x+(i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]

# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(borough_merged['Latitude'], borough_merged['Longitude'], borough_merged['Neighborhood'], borough_merged['Cluster Labels']):
    label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
    folium.CircleMarker(
        [lat, lon],
        radius=5,
        popup=label,
        color=rainbow[cluster-1],
        fill=True,
        fill_color=rainbow[cluster-1],
        fill_opacity=0.7).add_to(map_clusters)

map_clusters.save('borough_cluster_to.html')
map_clusters
Out[70]:
In [71]:
borough_merged.loc[borough_merged['Cluster Labels'] == 0, borough_merged.columns[[1] + list(range(5, borough_merged.shape[1]))]]
Out[71]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
0 Astoria Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant
1 Woodside Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center
2 Jackson Heights Latin American Restaurant Mobile Phone Shop South American Restaurant Peruvian Restaurant Mexican Restaurant Bakery Diner Kids Store Spanish Restaurant Thai Restaurant
3 Elmhurst Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop
4 Howard Beach Italian Restaurant Bagel Shop Sandwich Place Pharmacy Bank Spa Clothing Store Fast Food Restaurant Event Space Fried Chicken Joint
5 Corona Mexican Restaurant Empanada Restaurant Chinese Restaurant Sandwich Place Bakery Supermarket Restaurant Food Truck Park Italian Restaurant
7 Kew Gardens Chinese Restaurant Deli / Bodega Pizza Place Bar Indian Restaurant Donut Shop Bank Pet Store Movie Theater South American Restaurant
8 Richmond Hill Latin American Restaurant Pizza Place Discount Store Lounge Caribbean Restaurant Clothing Store Sandwich Place Diner Park Gym / Fitness Center
9 Flushing Chinese Restaurant Bubble Tea Shop Korean Restaurant Karaoke Bar Hotpot Restaurant Bakery Construction & Landscaping Supermarket Hotel Gym / Fitness Center
10 Long Island City Hotel Coffee Shop Pizza Place Mexican Restaurant Café Bar Donut Shop Deli / Bodega Gym / Fitness Center Gym
11 Sunnyside Pizza Place Italian Restaurant Chinese Restaurant Discount Store Coffee Shop South American Restaurant Bakery Hotel Supermarket Romanian Restaurant
12 East Elmhurst Donut Shop Rental Car Location Chinese Restaurant Lake Gas Station Electronics Store Coffee Shop Supermarket Ice Cream Shop Hotel Bar
17 Woodhaven Deli / Bodega Bank Pharmacy Park Donut Shop Liquor Store Bagel Shop Dive Bar Shoe Store Fast Food Restaurant
18 Ozone Park Pharmacy Pizza Place Deli / Bodega Diner Bank Gym Fast Food Restaurant Bowling Alley Sporting Goods Shop Breakfast Spot
19 South Ozone Park Deli / Bodega Park Bar Hotel Donut Shop Food Truck Fast Food Restaurant Sandwich Place Electronics Store Dumpling Restaurant
20 College Point Bakery Deli / Bodega Chinese Restaurant Pizza Place Bar Latin American Restaurant Pharmacy Supermarket Asian Restaurant Donut Shop
21 Whitestone Bubble Tea Shop Deli / Bodega Moving Target Train Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space
22 Bayside Bar Pizza Place Spa American Restaurant Donut Shop Indian Restaurant Sushi Restaurant South American Restaurant Pub Mexican Restaurant
23 Auburndale Fast Food Restaurant Train Station Donut Shop Korean Restaurant Noodle House Bar Discount Store Italian Restaurant Toy / Game Store Supermarket
24 Little Neck Bakery Coffee Shop Korean Restaurant Deli / Bodega Chinese Restaurant Italian Restaurant Spa Japanese Restaurant Bank Bar
25 Douglaston Deli / Bodega Chinese Restaurant Italian Restaurant Yoga Studio Coffee Shop Spa Fast Food Restaurant Shipping Store Supermarket Lounge
26 Glen Oaks Pharmacy Cosmetics Shop Grocery Store Mexican Restaurant Bus Station Fast Food Restaurant Donut Shop Sandwich Place Mobile Phone Shop Bank
27 Bellerose Chinese Restaurant Pizza Place Italian Restaurant Deli / Bodega Donut Shop Gas Station Salon / Barbershop Grocery Store Diner Bank
28 Kew Gardens Hills Bank Bus Station Playground Sushi Restaurant Pizza Place Candy Store Middle Eastern Restaurant Supermarket Bagel Shop Restaurant
29 Fresh Meadows Bus Station Chinese Restaurant Playground Vegetarian / Vegan Restaurant Grocery Store Moving Target Train Food Truck Pharmacy Pizza Place
30 Briarwood Deli / Bodega Pet Store Playground Mexican Restaurant Indian Restaurant Arts & Crafts Store Fast Food Restaurant Bus Station Coffee Shop Farm
31 Jamaica Center Mobile Phone Shop Caribbean Restaurant Clothing Store Donut Shop Coffee Shop Sandwich Place Performing Arts Venue Mexican Restaurant Pizza Place Men's Store
32 Oakland Gardens Korean Restaurant Chinese Restaurant Yoga Studio Donut Shop Supplement Shop Sushi Restaurant Supermarket Taiwanese Restaurant Bank Bagel Shop
33 Queens Village Bank Discount Store Fast Food Restaurant Bus Station Bus Stop Martial Arts Dojo Donut Shop Sandwich Place Mobile Phone Shop Bakery
35 South Jamaica Caribbean Restaurant Supermarket Grocery Store Shop & Service Park Bus Station Dessert Shop Bakery Farm Farmers Market
... ... ... ... ... ... ... ... ... ... ... ...
50 Neponsit Beach Bar Women's Store Farm Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farmers Market Eastern European Restaurant
51 Murray Hill Korean Restaurant Supermarket Coffee Shop Bar Japanese Restaurant Bath House Fish & Chips Shop Bank Bakery Karaoke Bar
52 Floral Park Indian Restaurant Grocery Store Basketball Court Pizza Place Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space
53 Holliswood Donut Shop Playground Supermarket Mobile Phone Shop Cycle Studio Eastern European Restaurant Flea Market Fish Market Fish & Chips Shop Cuban Restaurant
54 Jamaica Estates Dog Run Indian Restaurant Lounge Deli / Bodega Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space
55 Queensboro Hill Chinese Restaurant Bank Bakery Pizza Place Asian Restaurant Supermarket Taiwanese Restaurant Bar Bagel Shop Frozen Yogurt Shop
58 Lindenwood Hotel Japanese Restaurant Fast Food Restaurant Liquor Store Donut Shop Bank Gym Park Bakery Pizza Place
59 Laurelton Cosmetics Shop Caribbean Restaurant Train Station Park Sculpture Garden Dance Studio Dry Cleaner Fish Market Fish & Chips Shop Filipino Restaurant
60 Lefrak City Department Store Cosmetics Shop Bakery Deli / Bodega Fried Chicken Joint Furniture / Home Store Fruit & Vegetable Store Beer Bar Supplement Shop Supermarket
61 Belle Harbor Beach Deli / Bodega Boutique Spa Pharmacy Bagel Shop Ice Cream Shop Chinese Restaurant Italian Restaurant Donut Shop
62 Rockaway Park Beach Pizza Place Bank Steakhouse Bus Stop Board Shop French Restaurant Smoke Shop Seafood Restaurant Bagel Shop
63 Somerville Park Women's Store Falafel Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Farm Dry Cleaner
64 Brookville Deli / Bodega Women's Store Farmers Market Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farm Fast Food Restaurant
65 Bellaire Moving Target Deli / Bodega IT Services Convenience Store Construction & Landscaping Bus Station Laundromat Italian Restaurant Chinese Restaurant Halal Restaurant
66 North Corona Deli / Bodega Gym / Fitness Center Pizza Place Bakery Fast Food Restaurant Latin American Restaurant Caribbean Restaurant Mobile Phone Shop Museum Gym
67 Forest Hills Gardens Bakery Grocery Store Playground Park New American Restaurant Sandwich Place Tapas Restaurant Noodle House Mexican Restaurant Bagel Shop
68 Jamaica Hills Pharmacy Fried Chicken Joint Indian Restaurant Chinese Restaurant Fast Food Restaurant Donut Shop Thai Restaurant Coffee Shop Breakfast Spot Bus Line
69 Utopia Deli / Bodega Chinese Restaurant South American Restaurant Paper / Office Supplies Store Automotive Shop Basketball Court Donut Shop Bus Stop Playground Indie Movie Theater
70 Pomonok Bus Station Playground Japanese Restaurant Bowling Alley Supermarket Bar Scenic Lookout Pizza Place American Restaurant Deli / Bodega
71 Astoria Heights Chinese Restaurant Deli / Bodega Bakery Supermarket Italian Restaurant Bowling Alley Playground Plaza Pizza Place Burger Joint
74 Blissville Hotel Donut Shop Deli / Bodega Bus Station Clothing Store Rental Service Bar Electronics Store Restaurant Mattress Store
76 Middle Village Cosmetics Shop Italian Restaurant Playground Sports Bar Martial Arts Dojo Sushi Restaurant Sandwich Place Bank Bakery Discount Store
77 Malba Tennis Court Scenic Lookout Rest Area Women's Store Empanada Restaurant Dumpling Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Event Space
78 Hammels Beach Building Bus Station Shoe Store Gym / Fitness Center Dog Run Café Diner Bus Stop Fast Food Restaurant
79 Bayswater Tennis Court Playground Park Women's Store Event Space Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Falafel Restaurant
80 Queensbridge Hotel Sandwich Place Athletics & Sports Ramen Restaurant Baseball Field Roof Deck Park Scenic Lookout Hotel Bar Hotel Pool
81 Jamaica Performing Arts Venue Fried Chicken Joint Sandwich Place Discount Store Park Auto Garage Gym / Fitness Center Donut Shop Bus Station Pizza Place
82 Bellerose Manor Mobile Phone Shop Indian Restaurant Grocery Store Hookah Bar Farmers Market Fish & Chips Shop Fish Market Filipino Restaurant Fast Food Restaurant Dry Cleaner
84 Forest Park Music Venue Coffee Shop Theme Park Ride / Attraction Dog Run Lounge Track Park Donut Shop Bus Line Deli / Bodega
85 South Richmond Hill Indian Restaurant Caribbean Restaurant Pizza Place Park Bakery Supermarket Bar Event Space Eastern European Restaurant Egyptian Restaurant

73 rows × 11 columns

In [72]:
borough_merged.loc[borough_merged['Cluster Labels'] == 1, borough_merged.columns[[1] + list(range(5, borough_merged.shape[1]))]]
Out[72]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
6 Forest Hills Gym Yoga Studio Park Gym / Fitness Center Thai Restaurant Pizza Place Convenience Store Pharmacy Pool Deli / Bodega
14 Ridgewood Grocery Store Bank Restaurant Italian Restaurant Pizza Place Deli / Bodega Greek Restaurant Sushi Restaurant Noodle House Supplement Shop
15 Glendale Food & Drink Shop Arts & Crafts Store Brewery Liquor Store Park Dim Sum Restaurant Filipino Restaurant Fast Food Restaurant Farmers Market Fish & Chips Shop
43 Breezy Point Beach Monument / Landmark American Restaurant Board Shop Supermarket Trail Women's Store Farm Electronics Store Empanada Restaurant
57 Ravenswood Grocery Store Spanish Restaurant Liquor Store Chinese Restaurant Latin American Restaurant Sushi Restaurant Market Taco Place Fried Chicken Joint Korean Restaurant
75 Roxbury Beach Trail Pub Pizza Place Fast Food Restaurant Deli / Bodega Irish Pub Women's Store Eastern European Restaurant Egyptian Restaurant
83 Ditmars Steinway Deli / Bodega Greek Restaurant Bakery Italian Restaurant Bagel Shop Pizza Place Sushi Restaurant Grocery Store Bar Donut Shop
In [73]:
borough_merged.loc[borough_merged['Cluster Labels'] == 2, borough_merged.columns[[1] + list(range(5, borough_merged.shape[1]))]]
Out[73]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
73 Sunnyside Gardens Bar American Restaurant Grocery Store Coffee Shop Pizza Place Pharmacy Thai Restaurant Filipino Restaurant Deli / Bodega Mexican Restaurant
In [74]:
borough_merged.loc[borough_merged['Cluster Labels'] == 3, borough_merged.columns[[1] + list(range(5, borough_merged.shape[1]))]]
Out[74]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
16 Rego Park Bakery Chinese Restaurant Grocery Store Sandwich Place Donut Shop Sushi Restaurant Restaurant Pizza Place Pharmacy Martial Arts Dojo
In [75]:
borough_merged.loc[borough_merged['Cluster Labels'] == 4, borough_merged.columns[[1] + list(range(5, borough_merged.shape[1]))]]
Out[75]:
Neighborhood 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue
13 Maspeth Pizza Place Diner Mobile Phone Shop Grocery Store Bank Sandwich Place Sushi Restaurant Lounge Sports Bar Donut Shop
34 Hollis Sandwich Place Shopping Mall Bakery Fried Chicken Joint Park Chinese Restaurant Liquor Store Fast Food Restaurant Baseball Field Discount Store
56 Hillcrest Convenience Store Grocery Store Deli / Bodega Dim Sum Restaurant College Cafeteria College Basketball Court College Academic Building Coffee Shop Donut Shop Shipping Store
72 Hunters Point Italian Restaurant Café Japanese Restaurant Brewery Comedy Club Asian Restaurant Greek Restaurant Deli / Bodega Coffee Shop Burger Joint
In [76]:
borough_merged['Distance'] = ''
nyc_latlon = str(nyc_lat) + ',' + str(nyc_lon)
for i, row in borough_merged.iterrows():
    loc_latlon = borough_merged['Latitude'][i].astype(str) + ',' + borough_merged['Longitude'][i].astype(str)
    borough_merged['Distance'][i] = geopy.distance.geodesic((loc_latlon), (nyc_latlon)).miles
/home/jupyterlab/conda/lib/python3.6/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """
In [77]:
borough_merged
Out[77]:
Borough Neighborhood Latitude Longitude Cluster Labels 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue Distance
0 Queens Astoria 40.768509 -73.915654 0 Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant 4.56366
1 Queens Woodside 40.746349 -73.901842 0 Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center 4.60348
2 Queens Jackson Heights 40.751981 -73.882821 0 Latin American Restaurant Mobile Phone Shop South American Restaurant Peruvian Restaurant Mexican Restaurant Bakery Diner Kids Store Spanish Restaurant Thai Restaurant 5.6664
3 Queens Elmhurst 40.744049 -73.881656 0 Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop 5.61152
4 Queens Howard Beach 40.654225 -73.838138 0 Italian Restaurant Bagel Shop Sandwich Place Pharmacy Bank Spa Clothing Store Fast Food Restaurant Event Space Fried Chicken Joint 9.44563
5 Queens Corona 40.742382 -73.856825 0 Mexican Restaurant Empanada Restaurant Chinese Restaurant Sandwich Place Bakery Supermarket Restaurant Food Truck Park Italian Restaurant 6.88667
6 Queens Forest Hills 40.725264 -73.844475 1 Gym Yoga Studio Park Gym / Fitness Center Thai Restaurant Pizza Place Convenience Store Pharmacy Pool Deli / Bodega 7.49976
7 Queens Kew Gardens 40.705179 -73.829819 0 Chinese Restaurant Deli / Bodega Pizza Place Bar Indian Restaurant Donut Shop Bank Pet Store Movie Theater South American Restaurant 8.44835
8 Queens Richmond Hill 40.697947 -73.831833 0 Latin American Restaurant Pizza Place Discount Store Lounge Caribbean Restaurant Clothing Store Sandwich Place Diner Park Gym / Fitness Center 8.46544
9 Queens Flushing 40.764454 -73.831773 0 Chinese Restaurant Bubble Tea Shop Korean Restaurant Karaoke Bar Hotpot Restaurant Bakery Construction & Landscaping Supermarket Hotel Gym / Fitness Center 8.47724
10 Queens Long Island City 40.750217 -73.939202 0 Hotel Coffee Shop Pizza Place Mexican Restaurant Café Bar Donut Shop Deli / Bodega Gym / Fitness Center Gym 2.8492
11 Queens Sunnyside 40.740176 -73.926916 0 Pizza Place Italian Restaurant Chinese Restaurant Discount Store Coffee Shop South American Restaurant Bakery Hotel Supermarket Romanian Restaurant 3.2265
12 Queens East Elmhurst 40.764073 -73.867041 0 Donut Shop Rental Car Location Chinese Restaurant Lake Gas Station Electronics Store Coffee Shop Supermarket Ice Cream Shop Hotel Bar 6.70704
13 Queens Maspeth 40.725427 -73.896217 4 Pizza Place Diner Mobile Phone Shop Grocery Store Bank Sandwich Place Sushi Restaurant Lounge Sports Bar Donut Shop 4.78838
14 Queens Ridgewood 40.708323 -73.901435 1 Grocery Store Bank Restaurant Italian Restaurant Pizza Place Deli / Bodega Greek Restaurant Sushi Restaurant Noodle House Supplement Shop 4.76149
15 Queens Glendale 40.702762 -73.870742 1 Food & Drink Shop Arts & Crafts Store Brewery Liquor Store Park Dim Sum Restaurant Filipino Restaurant Fast Food Restaurant Farmers Market Fish & Chips Shop 6.41218
16 Queens Rego Park 40.728974 -73.857827 3 Bakery Chinese Restaurant Grocery Store Sandwich Place Donut Shop Sushi Restaurant Restaurant Pizza Place Pharmacy Martial Arts Dojo 6.78998
17 Queens Woodhaven 40.689887 -73.858110 0 Deli / Bodega Bank Pharmacy Park Donut Shop Liquor Store Bagel Shop Dive Bar Shoe Store Fast Food Restaurant 7.34206
18 Queens Ozone Park 40.680708 -73.843203 0 Pharmacy Pizza Place Deli / Bodega Diner Bank Gym Fast Food Restaurant Bowling Alley Sporting Goods Shop Breakfast Spot 8.31364
19 Queens South Ozone Park 40.668550 -73.809865 0 Deli / Bodega Park Bar Hotel Donut Shop Food Truck Fast Food Restaurant Sandwich Place Electronics Store Dumpling Restaurant 10.2554
20 Queens College Point 40.784903 -73.843045 0 Bakery Deli / Bodega Chinese Restaurant Pizza Place Bar Latin American Restaurant Pharmacy Supermarket Asian Restaurant Donut Shop 8.43097
21 Queens Whitestone 40.781291 -73.814202 0 Bubble Tea Shop Deli / Bodega Moving Target Train Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space 9.71938
22 Queens Bayside 40.766041 -73.774274 0 Bar Pizza Place Spa American Restaurant Donut Shop Indian Restaurant Sushi Restaurant South American Restaurant Pub Mexican Restaurant 11.4322
23 Queens Auburndale 40.761730 -73.791762 0 Fast Food Restaurant Train Station Donut Shop Korean Restaurant Noodle House Bar Discount Store Italian Restaurant Toy / Game Store Supermarket 10.4729
24 Queens Little Neck 40.770826 -73.738898 0 Bakery Coffee Shop Korean Restaurant Deli / Bodega Chinese Restaurant Italian Restaurant Spa Japanese Restaurant Bank Bar 13.3161
25 Queens Douglaston 40.766846 -73.742498 0 Deli / Bodega Chinese Restaurant Italian Restaurant Yoga Studio Coffee Shop Spa Fast Food Restaurant Shipping Store Supermarket Lounge 13.0768
26 Queens Glen Oaks 40.749441 -73.715481 0 Pharmacy Cosmetics Shop Grocery Store Mexican Restaurant Bus Station Fast Food Restaurant Donut Shop Sandwich Place Mobile Phone Shop Bank 14.3161
27 Queens Bellerose 40.728573 -73.720128 0 Chinese Restaurant Pizza Place Italian Restaurant Deli / Bodega Donut Shop Gas Station Salon / Barbershop Grocery Store Diner Bank 14.0177
28 Queens Kew Gardens Hills 40.722578 -73.820878 0 Bank Bus Station Playground Sushi Restaurant Pizza Place Candy Store Middle Eastern Restaurant Supermarket Bagel Shop Restaurant 8.74738
29 Queens Fresh Meadows 40.734394 -73.782713 0 Bus Station Chinese Restaurant Playground Vegetarian / Vegan Restaurant Grocery Store Moving Target Train Food Truck Pharmacy Pizza Place 10.7339
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
56 Queens Hillcrest 40.723825 -73.797603 4 Convenience Store Grocery Store Deli / Bodega Dim Sum Restaurant College Cafeteria College Basketball Court College Academic Building Coffee Shop Donut Shop Shipping Store 9.96223
57 Queens Ravenswood 40.761705 -73.931575 1 Grocery Store Spanish Restaurant Liquor Store Chinese Restaurant Latin American Restaurant Sushi Restaurant Market Taco Place Fried Chicken Joint Korean Restaurant 3.61071
58 Queens Lindenwood 40.663918 -73.849638 0 Hotel Japanese Restaurant Fast Food Restaurant Liquor Store Donut Shop Bank Gym Park Bakery Pizza Place 8.57299
59 Queens Laurelton 40.667884 -73.740256 0 Cosmetics Shop Caribbean Restaurant Train Station Park Sculpture Garden Dance Studio Dry Cleaner Fish Market Fish & Chips Shop Filipino Restaurant 13.675
60 Queens Lefrak City 40.736075 -73.862525 0 Department Store Cosmetics Shop Bakery Deli / Bodega Fried Chicken Joint Furniture / Home Store Fruit & Vegetable Store Beer Bar Supplement Shop Supermarket 6.55167
61 Queens Belle Harbor 40.576156 -73.854017 0 Beach Deli / Bodega Boutique Spa Pharmacy Bagel Shop Ice Cream Shop Chinese Restaurant Italian Restaurant Donut Shop 12.7636
62 Queens Rockaway Park 40.580343 -73.841534 0 Beach Pizza Place Bank Steakhouse Bus Stop Board Shop French Restaurant Smoke Shop Seafood Restaurant Bagel Shop 12.9008
63 Queens Somerville 40.597711 -73.796648 0 Park Women's Store Falafel Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Farm Dry Cleaner 13.5872
64 Queens Brookville 40.660003 -73.751753 0 Deli / Bodega Women's Store Farmers Market Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farm Fast Food Restaurant 13.2949
65 Queens Bellaire 40.733014 -73.738892 0 Moving Target Deli / Bodega IT Services Convenience Store Construction & Landscaping Bus Station Laundromat Italian Restaurant Chinese Restaurant Halal Restaurant 13.0323
66 Queens North Corona 40.754071 -73.857518 0 Deli / Bodega Gym / Fitness Center Pizza Place Bakery Fast Food Restaurant Latin American Restaurant Caribbean Restaurant Mobile Phone Shop Museum Gym 6.98962
67 Queens Forest Hills Gardens 40.714611 -73.841022 0 Bakery Grocery Store Playground Park New American Restaurant Sandwich Place Tapas Restaurant Noodle House Mexican Restaurant Bagel Shop 7.75319
68 Queens Jamaica Hills 40.711460 -73.796465 0 Pharmacy Fried Chicken Joint Indian Restaurant Chinese Restaurant Fast Food Restaurant Donut Shop Thai Restaurant Coffee Shop Breakfast Spot Bus Line 10.1002
69 Queens Utopia 40.733500 -73.796717 0 Deli / Bodega Chinese Restaurant South American Restaurant Paper / Office Supplies Store Automotive Shop Basketball Court Donut Shop Bus Stop Playground Indie Movie Theater 9.99784
70 Queens Pomonok 40.734936 -73.804861 0 Bus Station Playground Japanese Restaurant Bowling Alley Supermarket Bar Scenic Lookout Pizza Place American Restaurant Deli / Bodega 9.57271
71 Queens Astoria Heights 40.770317 -73.894680 0 Chinese Restaurant Deli / Bodega Bakery Supermarket Italian Restaurant Bowling Alley Playground Plaza Pizza Place Burger Joint 5.56429
72 Queens Hunters Point 40.743414 -73.953868 4 Italian Restaurant Café Japanese Restaurant Brewery Comedy Club Asian Restaurant Greek Restaurant Deli / Bodega Coffee Shop Burger Joint 1.95007
73 Queens Sunnyside Gardens 40.745652 -73.918193 2 Bar American Restaurant Grocery Store Coffee Shop Pizza Place Pharmacy Thai Restaurant Filipino Restaurant Deli / Bodega Mexican Restaurant 3.76067
74 Queens Blissville 40.737251 -73.932442 0 Hotel Donut Shop Deli / Bodega Bus Station Clothing Store Rental Service Bar Electronics Store Restaurant Mattress Store 2.90548
75 Queens Roxbury 40.567376 -73.892138 1 Beach Trail Pub Pizza Place Fast Food Restaurant Deli / Bodega Irish Pub Women's Store Eastern European Restaurant Egyptian Restaurant 12.3367
76 Queens Middle Village 40.716415 -73.881143 0 Cosmetics Shop Italian Restaurant Playground Sports Bar Martial Arts Dojo Sushi Restaurant Sandwich Place Bank Bakery Discount Store 5.65392
77 Queens Malba 40.790602 -73.826678 0 Tennis Court Scenic Lookout Rest Area Women's Store Empanada Restaurant Dumpling Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Event Space 9.37485
78 Queens Hammels 40.587338 -73.805530 0 Beach Building Bus Station Shoe Store Gym / Fitness Center Dog Run Café Diner Bus Stop Fast Food Restaurant 13.7537
79 Queens Bayswater 40.611322 -73.765968 0 Tennis Court Playground Park Women's Store Event Space Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Falafel Restaurant 14.2506
80 Queens Queensbridge 40.756091 -73.945631 0 Hotel Sandwich Place Athletics & Sports Ramen Restaurant Baseball Field Roof Deck Park Scenic Lookout Hotel Bar Hotel Pool 2.78927
81 Queens Jamaica 40.702677 -73.788969 0 Performing Arts Venue Fried Chicken Joint Sandwich Place Discount Store Park Auto Garage Gym / Fitness Center Donut Shop Bus Station Pizza Place 10.5855
82 Queens Bellerose Manor 40.732021 -73.718364 0 Mobile Phone Shop Indian Restaurant Grocery Store Hookah Bar Farmers Market Fish & Chips Shop Fish Market Filipino Restaurant Fast Food Restaurant Dry Cleaner 14.1093
83 Queens Ditmars Steinway 40.776741 -73.909528 1 Deli / Bodega Greek Restaurant Bakery Italian Restaurant Bagel Shop Pizza Place Sushi Restaurant Grocery Store Bar Donut Shop 5.15894
84 Queens Forest Park 40.700023 -73.856609 0 Music Venue Coffee Shop Theme Park Ride / Attraction Dog Run Lounge Track Park Donut Shop Bus Line Deli / Bodega 7.17687
85 Queens South Richmond Hill 40.691893 -73.825733 0 Indian Restaurant Caribbean Restaurant Pizza Place Park Bakery Supermarket Bar Event Space Eastern European Restaurant Egyptian Restaurant 8.89207

86 rows × 16 columns

In [78]:
borough_merged.drop(['Borough', 'Latitude', 'Longitude'], axis=1, inplace=True)
crime_aggr.drop(['Borough'], axis=1, inplace=True)
borough_merged.set_index('Neighborhood')
crime_aggr.set_index('Neighborhood')
borough_venues_aggr.set_index('Neighborhood')
from functools import reduce
data_frames = [borough_merged, crime_aggr, borough_venues_aggr]
queens_data = reduce(lambda  left,right: pd.merge(left,right,on=['Neighborhood'],
                                                  how='left'), data_frames).fillna(0)
In [79]:
data_frames = [borough_merged, crime_aggr, borough_venues_aggr]
queens_data = reduce(lambda  left,right: pd.merge(left,right,on=['Neighborhood'],
                                                  how='left'), data_frames).fillna(0)
queens_data.head(10)
Out[79]:
Neighborhood Cluster Labels 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue Distance Offense Venue
0 Astoria 0 Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant 4.563659 5.0 100
1 Woodside 0 Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center 4.603483 5.0 74
2 Jackson Heights 0 Latin American Restaurant Mobile Phone Shop South American Restaurant Peruvian Restaurant Mexican Restaurant Bakery Diner Kids Store Spanish Restaurant Thai Restaurant 5.666396 7.0 84
3 Elmhurst 0 Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop 5.611521 4.0 34
4 Howard Beach 0 Italian Restaurant Bagel Shop Sandwich Place Pharmacy Bank Spa Clothing Store Fast Food Restaurant Event Space Fried Chicken Joint 9.445633 0.0 41
5 Corona 0 Mexican Restaurant Empanada Restaurant Chinese Restaurant Sandwich Place Bakery Supermarket Restaurant Food Truck Park Italian Restaurant 6.886668 6.0 18
6 Forest Hills 1 Gym Yoga Studio Park Gym / Fitness Center Thai Restaurant Pizza Place Convenience Store Pharmacy Pool Deli / Bodega 7.499762 3.0 37
7 Kew Gardens 0 Chinese Restaurant Deli / Bodega Pizza Place Bar Indian Restaurant Donut Shop Bank Pet Store Movie Theater South American Restaurant 8.448352 0.0 46
8 Richmond Hill 0 Latin American Restaurant Pizza Place Discount Store Lounge Caribbean Restaurant Clothing Store Sandwich Place Diner Park Gym / Fitness Center 8.465440 2.0 22
9 Flushing 0 Chinese Restaurant Bubble Tea Shop Korean Restaurant Karaoke Bar Hotpot Restaurant Bakery Construction & Landscaping Supermarket Hotel Gym / Fitness Center 8.477245 50.0 67
In [80]:
queens_data[queens_data['Cluster Labels'] == 0]
Out[80]:
Neighborhood Cluster Labels 1st Most Common Venue 2nd Most Common Venue 3rd Most Common Venue 4th Most Common Venue 5th Most Common Venue 6th Most Common Venue 7th Most Common Venue 8th Most Common Venue 9th Most Common Venue 10th Most Common Venue Distance Offense Venue
0 Astoria 0 Middle Eastern Restaurant Bar Greek Restaurant Hookah Bar Bakery Seafood Restaurant Pub Italian Restaurant Gym / Fitness Center Latin American Restaurant 4.563659 5.0 100
1 Woodside 0 Grocery Store Bakery Thai Restaurant Pub Pizza Place Bar Donut Shop Latin American Restaurant American Restaurant Gym / Fitness Center 4.603483 5.0 74
2 Jackson Heights 0 Latin American Restaurant Mobile Phone Shop South American Restaurant Peruvian Restaurant Mexican Restaurant Bakery Diner Kids Store Spanish Restaurant Thai Restaurant 5.666396 7.0 84
3 Elmhurst 0 Thai Restaurant Mexican Restaurant Chinese Restaurant Bubble Tea Shop Vietnamese Restaurant South American Restaurant Hotpot Restaurant Gym / Fitness Center Colombian Restaurant Salon / Barbershop 5.611521 4.0 34
4 Howard Beach 0 Italian Restaurant Bagel Shop Sandwich Place Pharmacy Bank Spa Clothing Store Fast Food Restaurant Event Space Fried Chicken Joint 9.445633 0.0 41
5 Corona 0 Mexican Restaurant Empanada Restaurant Chinese Restaurant Sandwich Place Bakery Supermarket Restaurant Food Truck Park Italian Restaurant 6.886668 6.0 18
7 Kew Gardens 0 Chinese Restaurant Deli / Bodega Pizza Place Bar Indian Restaurant Donut Shop Bank Pet Store Movie Theater South American Restaurant 8.448352 0.0 46
8 Richmond Hill 0 Latin American Restaurant Pizza Place Discount Store Lounge Caribbean Restaurant Clothing Store Sandwich Place Diner Park Gym / Fitness Center 8.465440 2.0 22
9 Flushing 0 Chinese Restaurant Bubble Tea Shop Korean Restaurant Karaoke Bar Hotpot Restaurant Bakery Construction & Landscaping Supermarket Hotel Gym / Fitness Center 8.477245 50.0 67
10 Long Island City 0 Hotel Coffee Shop Pizza Place Mexican Restaurant Café Bar Donut Shop Deli / Bodega Gym / Fitness Center Gym 2.849199 21.0 68
11 Sunnyside 0 Pizza Place Italian Restaurant Chinese Restaurant Discount Store Coffee Shop South American Restaurant Bakery Hotel Supermarket Romanian Restaurant 3.226495 0.0 42
12 East Elmhurst 0 Donut Shop Rental Car Location Chinese Restaurant Lake Gas Station Electronics Store Coffee Shop Supermarket Ice Cream Shop Hotel Bar 6.707041 0.0 13
17 Woodhaven 0 Deli / Bodega Bank Pharmacy Park Donut Shop Liquor Store Bagel Shop Dive Bar Shoe Store Fast Food Restaurant 7.342060 1.0 25
18 Ozone Park 0 Pharmacy Pizza Place Deli / Bodega Diner Bank Gym Fast Food Restaurant Bowling Alley Sporting Goods Shop Breakfast Spot 8.313642 4.0 33
19 South Ozone Park 0 Deli / Bodega Park Bar Hotel Donut Shop Food Truck Fast Food Restaurant Sandwich Place Electronics Store Dumpling Restaurant 10.255431 2.0 11
20 College Point 0 Bakery Deli / Bodega Chinese Restaurant Pizza Place Bar Latin American Restaurant Pharmacy Supermarket Asian Restaurant Donut Shop 8.430966 3.0 46
21 Whitestone 0 Bubble Tea Shop Deli / Bodega Moving Target Train Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space 9.719382 0.0 4
22 Bayside 0 Bar Pizza Place Spa American Restaurant Donut Shop Indian Restaurant Sushi Restaurant South American Restaurant Pub Mexican Restaurant 11.432189 1.0 74
23 Auburndale 0 Fast Food Restaurant Train Station Donut Shop Korean Restaurant Noodle House Bar Discount Store Italian Restaurant Toy / Game Store Supermarket 10.472948 0.0 21
24 Little Neck 0 Bakery Coffee Shop Korean Restaurant Deli / Bodega Chinese Restaurant Italian Restaurant Spa Japanese Restaurant Bank Bar 13.316142 1.0 47
25 Douglaston 0 Deli / Bodega Chinese Restaurant Italian Restaurant Yoga Studio Coffee Shop Spa Fast Food Restaurant Shipping Store Supermarket Lounge 13.076819 0.0 21
26 Glen Oaks 0 Pharmacy Cosmetics Shop Grocery Store Mexican Restaurant Bus Station Fast Food Restaurant Donut Shop Sandwich Place Mobile Phone Shop Bank 14.316069 1.0 21
27 Bellerose 0 Chinese Restaurant Pizza Place Italian Restaurant Deli / Bodega Donut Shop Gas Station Salon / Barbershop Grocery Store Diner Bank 14.017741 0.0 19
28 Kew Gardens Hills 0 Bank Bus Station Playground Sushi Restaurant Pizza Place Candy Store Middle Eastern Restaurant Supermarket Bagel Shop Restaurant 8.747384 0.0 22
29 Fresh Meadows 0 Bus Station Chinese Restaurant Playground Vegetarian / Vegan Restaurant Grocery Store Moving Target Train Food Truck Pharmacy Pizza Place 10.733922 2.0 15
30 Briarwood 0 Deli / Bodega Pet Store Playground Mexican Restaurant Indian Restaurant Arts & Crafts Store Fast Food Restaurant Bus Station Coffee Shop Farm 9.310820 0.0 10
31 Jamaica Center 0 Mobile Phone Shop Caribbean Restaurant Clothing Store Donut Shop Coffee Shop Sandwich Place Performing Arts Venue Mexican Restaurant Pizza Place Men's Store 10.150977 0.0 40
32 Oakland Gardens 0 Korean Restaurant Chinese Restaurant Yoga Studio Donut Shop Supplement Shop Sushi Restaurant Supermarket Taiwanese Restaurant Bank Bagel Shop 12.229900 1.0 28
33 Queens Village 0 Bank Discount Store Fast Food Restaurant Bus Station Bus Stop Martial Arts Dojo Donut Shop Sandwich Place Mobile Phone Shop Bakery 13.068264 4.0 18
35 South Jamaica 0 Caribbean Restaurant Supermarket Grocery Store Shop & Service Park Bus Station Dessert Shop Bakery Farm Farmers Market 10.591538 0.0 9
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
50 Neponsit 0 Beach Bar Women's Store Farm Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farmers Market Eastern European Restaurant 12.903449 0.0 5
51 Murray Hill 0 Korean Restaurant Supermarket Coffee Shop Bar Japanese Restaurant Bath House Fish & Chips Shop Bank Bakery Karaoke Bar 9.435281 0.0 43
52 Floral Park 0 Indian Restaurant Grocery Store Basketball Court Pizza Place Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space 14.625633 0.0 6
53 Holliswood 0 Donut Shop Playground Supermarket Mobile Phone Shop Cycle Studio Eastern European Restaurant Flea Market Fish Market Fish & Chips Shop Cuban Restaurant 11.569875 0.0 4
54 Jamaica Estates 0 Dog Run Indian Restaurant Lounge Deli / Bodega Women's Store Falafel Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space 10.540343 0.0 4
55 Queensboro Hill 0 Chinese Restaurant Bank Bakery Pizza Place Asian Restaurant Supermarket Taiwanese Restaurant Bar Bagel Shop Frozen Yogurt Shop 8.521099 0.0 25
58 Lindenwood 0 Hotel Japanese Restaurant Fast Food Restaurant Liquor Store Donut Shop Bank Gym Park Bakery Pizza Place 8.572991 0.0 12
59 Laurelton 0 Cosmetics Shop Caribbean Restaurant Train Station Park Sculpture Garden Dance Studio Dry Cleaner Fish Market Fish & Chips Shop Filipino Restaurant 13.675018 1.0 7
60 Lefrak City 0 Department Store Cosmetics Shop Bakery Deli / Bodega Fried Chicken Joint Furniture / Home Store Fruit & Vegetable Store Beer Bar Supplement Shop Supermarket 6.551668 0.0 29
61 Belle Harbor 0 Beach Deli / Bodega Boutique Spa Pharmacy Bagel Shop Ice Cream Shop Chinese Restaurant Italian Restaurant Donut Shop 12.763564 0.0 18
62 Rockaway Park 0 Beach Pizza Place Bank Steakhouse Bus Stop Board Shop French Restaurant Smoke Shop Seafood Restaurant Bagel Shop 12.900791 2.0 24
63 Somerville 0 Park Women's Store Falafel Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Farm Dry Cleaner 13.587223 0.0 1
64 Brookville 0 Deli / Bodega Women's Store Farmers Market Egyptian Restaurant Electronics Store Empanada Restaurant Event Space Falafel Restaurant Farm Fast Food Restaurant 13.294853 0.0 1
65 Bellaire 0 Moving Target Deli / Bodega IT Services Convenience Store Construction & Landscaping Bus Station Laundromat Italian Restaurant Chinese Restaurant Halal Restaurant 13.032311 0.0 12
66 North Corona 0 Deli / Bodega Gym / Fitness Center Pizza Place Bakery Fast Food Restaurant Latin American Restaurant Caribbean Restaurant Mobile Phone Shop Museum Gym 6.989616 2.0 22
67 Forest Hills Gardens 0 Bakery Grocery Store Playground Park New American Restaurant Sandwich Place Tapas Restaurant Noodle House Mexican Restaurant Bagel Shop 7.753192 0.0 22
68 Jamaica Hills 0 Pharmacy Fried Chicken Joint Indian Restaurant Chinese Restaurant Fast Food Restaurant Donut Shop Thai Restaurant Coffee Shop Breakfast Spot Bus Line 10.100195 0.0 27
69 Utopia 0 Deli / Bodega Chinese Restaurant South American Restaurant Paper / Office Supplies Store Automotive Shop Basketball Court Donut Shop Bus Stop Playground Indie Movie Theater 9.997842 0.0 17
70 Pomonok 0 Bus Station Playground Japanese Restaurant Bowling Alley Supermarket Bar Scenic Lookout Pizza Place American Restaurant Deli / Bodega 9.572708 0.0 10
71 Astoria Heights 0 Chinese Restaurant Deli / Bodega Bakery Supermarket Italian Restaurant Bowling Alley Playground Plaza Pizza Place Burger Joint 5.564285 0.0 13
74 Blissville 0 Hotel Donut Shop Deli / Bodega Bus Station Clothing Store Rental Service Bar Electronics Store Restaurant Mattress Store 2.905480 0.0 20
76 Middle Village 0 Cosmetics Shop Italian Restaurant Playground Sports Bar Martial Arts Dojo Sushi Restaurant Sandwich Place Bank Bakery Discount Store 5.653918 1.0 16
77 Malba 0 Tennis Court Scenic Lookout Rest Area Women's Store Empanada Restaurant Dumpling Restaurant Eastern European Restaurant Egyptian Restaurant Electronics Store Event Space 9.374853 0.0 3
78 Hammels 0 Beach Building Bus Station Shoe Store Gym / Fitness Center Dog Run Café Diner Bus Stop Fast Food Restaurant 13.753728 0.0 19
79 Bayswater 0 Tennis Court Playground Park Women's Store Event Space Eastern European Restaurant Egyptian Restaurant Electronics Store Empanada Restaurant Falafel Restaurant 14.250638 0.0 3
80 Queensbridge 0 Hotel Sandwich Place Athletics & Sports Ramen Restaurant Baseball Field Roof Deck Park Scenic Lookout Hotel Bar Hotel Pool 2.789271 0.0 17
81 Jamaica 0 Performing Arts Venue Fried Chicken Joint Sandwich Place Discount Store Park Auto Garage Gym / Fitness Center Donut Shop Bus Station Pizza Place 10.585457 70.0 10
82 Bellerose Manor 0 Mobile Phone Shop Indian Restaurant Grocery Store Hookah Bar Farmers Market Fish & Chips Shop Fish Market Filipino Restaurant Fast Food Restaurant Dry Cleaner 14.109318 0.0 5
84 Forest Park 0 Music Venue Coffee Shop Theme Park Ride / Attraction Dog Run Lounge Track Park Donut Shop Bus Line Deli / Bodega 7.176873 0.0 12
85 South Richmond Hill 0 Indian Restaurant Caribbean Restaurant Pizza Place Park Bakery Supermarket Bar Event Space Eastern European Restaurant Egyptian Restaurant 8.892066 1.0 14

73 rows × 15 columns

In [ ]: